/*

DATE: June 18 2018

PROJECT: Police killings and mental health of black Americans, replication archive

PURPOSE: This file uses source data from the Mapping Police Violence (MPV) project (extract current as of October 2016)
to create the following:

(1) An overall cleaned file with all police killings in the database
(2) A file with all police killings of unarmed black Americans - the main exposure
(3) A file with all police killings of allegedly armed black Americans - specificity analysis
(4) A file with all police killings of unarmed white Americans - specificity analysis

*/


***CREATE (1)****

**FILEPATHS (User to delineate their own [FILE PATH] here!)
global source "[FILE PATH]/Source Datasets and Do Files/"
global working "[FILE PATH]/Working Datasets/"

**(A) IMPORT EXCEL FILE FROM MPV CONTAINING POLICE KILLINGS DATABASE
import excel "$source/MPVDatasetDownload.xlsx", sheet("2013-2016 Police Killings") firstrow

**(B) CLEAN, LABEL DATA. INCLUDE STATE FIPS CODES
*Create date variable
gen month = month(Dateofinjuryresultingindeat)
gen year = year(Dateofinjuryresultingindeat)
gen day = day(Dateofinjuryresultingindeat)

*Generate race variable
gen race = .
recode race (.=2) if Victimsrace=="Black"
recode race (.=1) if Victimsrace=="White"
recode race (.=3) if Victimsrace~="Black"&Victimsrace~="White"

label define race1 1 "White" 2 "Black" 3 "Other"
label values race race1

*Merge in state fips codes
rename Locationofdeathstate abbrev
joinby abbrev using "$source/abbtofip.dta", unmatched(master) _merge(_mergeSTFIP)
drop _mergeSTFIP

drop T-BB

**(C) Save working file
save "$working/MPV_data_master", replace
clear

***CREATE (2)****
use "$working/MPV_data_master.dta", clear

gen count = 1
keep if Unarmed=="Unarmed"

collapse (sum) count, by(year month day race stfip)

destring day month year stfip, force replace
gen shooting_date = mdy(month, day, year)
gen date = shooting_date

keep if race==2

drop if stfip==.

save "$working/black_unarmed_shootings.dta", replace
clear

***CREATE (3)****
use "$working/MPV_data_master.dta", clear

gen count = 1
keep if Unarmed~="Unarmed"

collapse (sum) count, by(year month day race stfip)

destring day month year stfip, force replace
gen shooting_date = mdy(month, day, year)
gen date = shooting_date

keep if race==2

drop if stfip==.

save "$data/black_armed_shootings.dta", replace
clear

***CREATE (4)****
use "$working/MPV_data_master.dta", clear

gen count = 1
keep if Unarmed=="Unarmed"

collapse (sum) count, by(year month day race stfip)

destring day month year stfip, force replace
gen shooting_date = mdy(month, day, year)
gen date = shooting_date

keep if race==1

drop if stfip==.

save "$data/white_unarmed_shootings.dta", replace
clear



